package queries;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import entitys.Type;

/**
 * returns list of product types from ccrm.type
 * @author Dima
 *
 */
public class GetProductTypesListQuery {
	public static ArrayList<Type> exe(Connection conn) {
		ArrayList<Type> arr = new ArrayList<Type>();
		ResultSet res = null; 
		Statement stmt;

			try {
				stmt = conn.createStatement();
				res = stmt.executeQuery("SELECT * from ccrm.type; ");
				
				while(res.next())
				{
					arr.add(new Type(res.getString(2), res.getInt(1)));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Error in retrieving types");
			}
			try {
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		return arr;
		
	}
	
	
	public static ArrayList<Type> exe(Connection conn,Type type) { // with sector condition
		ArrayList<Type> arr = new ArrayList<Type>();
		ResultSet res = null; 
		PreparedStatement pstmt;

			try {
				pstmt = conn.prepareStatement("SELECT * from ccrm.type where type.name like ?;");
				pstmt.setString(1,"%"+ type.getTypeName()+"%");
				res = pstmt.executeQuery();
				
				while(res.next())
				{
					arr.add(new Type(res.getString(2), res.getInt(1)));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Error in retrieving types");
			}
			try {
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		return arr;
		
	}
	

}
